﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ShopfloorClass;
using System.Data;
using db;

namespace MasterTesting
{
    public struct tMasterTestingResult
    {
        public DateTime time_Start;
        public DateTime time_End;
        public int qty_master;
        
        public void init(int qty)
        {
            time_Start = new DateTime();
            time_End = new DateTime();
        }
    }

    public static class MasterTestingFunctions
    {
        /// <summary>获取最近使用的balance的Multiplier
        /// </summary>
        /// <param name="Testplan"></param>
        /// <param name="Qs_Qd">Qs=0,Qd=1</param>
        /// <param name="tablename">cr1T040,cr2T040,cr3line1</param>
        /// <returns></returns>
        public static string[] GetRecentMulti(string Testplan, string tablename,string endtime)
        {
            string sSql;
            DataTable dt = new DataTable();
            string[] QsQdMulti = new string[2]{"0","0"};
            string endtime0, endtime0_year, endtime0_month;
            string endtime1, endtime1_year, endtime1_month;
            
            endtime0 = endtime.Substring(0, 6);
            endtime0_year =endtime0.Substring (0,4);
            endtime0_month =endtime0 .Substring (4,2);

            switch (endtime0_month)
            { 
                case "00":
                    endtime1_year = (Convert.ToInt32(endtime0_year) - 1).ToString("0000");
                    endtime1_month = "12";
                    break;
                case "01":
                    endtime1_year = (Convert.ToInt32(endtime0_year) - 1).ToString("0000");
                    endtime1_month = "12";
                    break;
                default :
                    endtime1_year = endtime0_year;
                    endtime1_month =(Convert .ToInt32 ( endtime0_month) - 1).ToString ("00");
                    break;
            }
            endtime1 = endtime1_year + endtime1_month;


            switch (tablename)
            {
                case "cr3line1": sSql = string.Format(@"SELECT 
{0}_{2}.1404300113 as 'Qs\r\nBal\r\nMult', {0}_{2}.1404300112 as 'Qd\r\nBal\r\nMult'
FROM {0}_{2} 
WHERE type_id={1} AND FamilyCode = '2' order by {0}_{2}.offload desc limit 1", tablename, Testplan,endtime0);
                    dt = dbFunction.Query(ShopfloorDB.conn, sSql);
                    if (dt.Rows.Count == 0)
                    {
                        sSql = string.Format(@"SELECT 
{0}_{2}.1404300113 as 'Qs\r\nBal\r\nMult', {0}_{2}.1404300112 as 'Qd\r\nBal\r\nMult'
FROM {0}_{2} 
WHERE type_id={1} AND FamilyCode = '2' order by {0}_{2}.offload desc limit 1", tablename, Testplan,endtime1);
                    }
                    break;
                default: sSql = string.Format(@"SELECT 
{0}_{2}.obj70 as 'Qs\r\nBal\r\nMult', {0}_{2}.obj69 as 'Qd\r\nBal\r\nMult'
FROM {0}_{2} 
WHERE type_id={1} AND FamilyCode = '2' order by {0}_{2}.offload desc limit 1", tablename, Testplan,endtime0);
                    if (dt.Rows.Count == 0)
                    {
                        sSql = string.Format(@"SELECT 
{0}_{2}.obj70 as 'Qs\r\nBal\r\nMult', {0}_{2}.obj69 as 'Qd\r\nBal\r\nMult'
FROM {0}_{2} 
WHERE type_id={1} AND FamilyCode = '2' order by {0}_{2}.offload desc limit 1", tablename, Testplan, endtime1);
                    }
                    break;
            }
            dt = dbFunction.Query(ShopfloorDB.conn, sSql);
            if (dt.Rows.Count > 0)
            {
                QsQdMulti[0] = dt.Rows[0]["Qs\r\nBal\r\nMult"].ToString();
                QsQdMulti[1] = dt.Rows[0]["Qd\r\nBal\r\nMult"].ToString();
            }
            return QsQdMulti;
        }

        /// <summary>根据某个时间段内某个testplan的各个master测试数据的个数，计算出一共进行了几次master测试
        /// </summary>
        public static int GetTestingTimes(string starttime, string endtime, string Testplan, string tablename)
        {
            string sSql;
            DataTable dt = new DataTable();
            int iQty = 0;
            sSql = string.Format(
@"select max(qty)from (select  count(*)as qty, partid  from {3} where
familycode=2 and
type_id={0} and
offload between {1}.1 and {2}
group by partid)as q1"
, Testplan, starttime, endtime, tablename);
            dt = dbFunction.Query(ShopfloorDB.conn, sSql);
            if (dt.Rows.Count > 0) iQty = Convert.ToInt32(dt.Rows[0].ItemArray[0].ToString());
            return iQty;
        }

        /// <summary>查询某个时间段内测试的某个testplan的master的ID
        /// </summary>
        public static string[] GetMasterIDs(string starttime, string endtime, string Testplan, string tablename)
        {
            string sSql;
            DataTable dt;
            List<string> list = new List<string>();
            sSql =string.Format (@"
(select  partid  from {0} where
familycode=2 and
type_id={1} and
offload between {2}.1 and {3}
group by partid)
",tablename ,Testplan ,starttime ,endtime);
            dt=dbFunction.Query(ShopfloorDB.conn, sSql);
            if (dt.Rows.Count  > 0)
            { 
                foreach (DataRow row in dt.Rows) 
                {
                    list.Add(row.ItemArray[0].ToString());
                }
            }
            return list.ToArray();
        }

        /// <summary>查询某个时间段内，根据multiplier确定master测试的开始时间点
        /// </summary>
        public static string[] GetTestingPeriods(string starttime, string endtime, string Testplan, string tablename)
        {
            DataTable dt = new DataTable();
            List<string> listStartTime = new List<string>();
            int i, j, k = 0;
            bool bMultiChanged = false;
            string Up, Down;

            dt = GetMasterTestResult_BAL(starttime, endtime, Testplan, tablename);
            listStartTime.Add(starttime);
            for (i = 0; i < dt.Rows.Count - 1; i++)
            {
                Up = dt.Rows[i].ItemArray[7].ToString();
                Down = dt.Rows[i + 1].ItemArray[7].ToString();
                bMultiChanged = bMultiChanged || (Up != Down);//7_Qs_Fm_Mult

                Up = dt.Rows[i].ItemArray[8].ToString();
                Down = dt.Rows[i + 1].ItemArray[8].ToString();
                bMultiChanged = bMultiChanged || (Up != Down); ;//8_Qd_Fm_Mult

                Up = dt.Rows[i].ItemArray[9].ToString();
                Down = dt.Rows[i + 1].ItemArray[9].ToString();
                bMultiChanged = bMultiChanged || (Up != Down);//9_Qs_Bal_Mult

                Up = dt.Rows[i].ItemArray[10].ToString();
                Down = dt.Rows[i + 1].ItemArray[10].ToString();
                bMultiChanged = bMultiChanged || (Up != Down);//10_Qd_Bal_Mult

                if (bMultiChanged)
                {
                    //System.Windows.Forms.MessageBox.Show(string.Format(@"{0},{1} in {2}", dt1, dt2, i.ToString()));
                    listStartTime.Add(dt.Rows[i].ItemArray[2].ToString());
                    bMultiChanged = false;
                }
            }

            //*---------把每个时间段内如果有重复的，再次分组-----------------
            Int64 nextStart = 21001231235959;
            //Int64 offloadTmp;
            int index = -1;
            Int64 jOffload,kOffload;
            string jPartID,kPartID;

           // while (true)
            {
                nextStart = 21001231235959;
                          //20130806004305
                int count1 = listStartTime.Count;
                for (i = 0; i < count1; i++)
                {
                    if (i == listStartTime.Count - 1)//如果已经是最后一个时间截点了，则最终时间截点就选择为界面里的endtime
                        dt = GetMasterTestResult_BAL(listStartTime.ToArray()[i].Trim('*', '#'), endtime.Trim('*', '#'), Testplan, tablename);
                    else
                        dt = GetMasterTestResult_BAL(listStartTime.ToArray()[i].Trim('*', '#'), (Convert.ToInt64(listStartTime.ToArray()[i + 1].Trim('*', '#'))).ToString(), Testplan, tablename);
                    for (j = 0; j < dt.Rows.Count; j++)
                    {
                        jOffload = Convert.ToInt64(dt.Rows[j]["offload"].ToString ());
                        jPartID =dt.Rows[j]["PartID"].ToString ();
                        for (k = j + 1; k < dt.Rows.Count; k++)
                        {
                            kOffload = Convert.ToInt64(dt.Rows[k]["offload"].ToString());
                            kPartID = dt.Rows[k]["PartID"].ToString();
                            if (jPartID == kPartID)
                            {
                                if (kOffload < nextStart)
                                {
                                    nextStart = kOffload;
                                    index = k;
                                }

                            }
                        }
                        if (jOffload > nextStart) break;

                    }
                    //if (nextStart < 21001231235959)
                    if (index > 0) 
                    {
                        
                        listStartTime = AddDate(listStartTime,dt.Rows [index -1]["Offload"].ToString ());
                        count1++;
                        nextStart = 21001231235959;
                        index = -1;
                    }
                }
               // if (nextStart == 21001231235959) break;
               // else nextStart = 21001231235959;
            }
            /*----------------------------------------*/


            return listStartTime.ToArray();
        }

        private static List<string> AddDate(List<string> SourceList, string needadd)
        {
            List<string> list = new List<string>();
            list = SourceList;


            Int64 datecode;
            int i;
            if (needadd.Length != "20130803002930".Length)
            {
                return list;
            }
            datecode = Convert.ToInt64(needadd);
            for (i = 0; i < list.Count; i++)
            {
                if (Convert.ToInt64( list[i] .ToString().Trim('*', '#')) == datecode)
                {
                    return list;
                }
                if (Convert.ToInt64(list[i].ToString().Trim('*', '#')) > datecode)
                {
                    list.Insert(i, datecode.ToString() + "#");
                    return list;
                }
            }
            if (i == list.Count)
            {
                list.Add(datecode.ToString() + "#");
                return list ;
            }
            return list;
        
        }

        /// <summary>获取Master的nominal值
        /// </summary>
        /// <param name="testplan"></param>
        /// <param name="NominalType">0：Qs，1：Qd</param>
        /// <returns></returns>
        public static double GetMasterNominal(string testplan, int NominalType)
        {
            string sSql;
            double dTemp = -1;
            DataTable dt = new DataTable();
            switch (NominalType)
            {
                case 1:
                    sSql = string.Format("select dynamic from targets where type_id ={0} and family = 'deka7'", testplan);
                    break;
                default:
                    sSql = string.Format("select static from targets where type_id ={0} and family = 'deka7'", testplan);
                    break;
            }
            dt = dbFunction.Query(ShopfloorDB.conn, sSql);
            if (dt.Rows.Count > 0) dTemp = Convert.ToDouble(dt.Rows[0].ItemArray[0].ToString());

            return dTemp;
        }

        /// <summary>查询3条线线OP40的Balance测试结果
        /// </summary>
        /// <param name="starttime"></param>
        /// <param name="endtime"></param>
        /// <param name="Testplan"></param>
        /// <param name="QsNomi"></param>
        /// <param name="QdNomi"></param>
        /// <param name="tablename">可以为cr1T040,cr2T040,cr3line1</param>
        /// <returns></returns>
        public static DataTable GetMasterTestResult_BAL(string starttime, string endtime, string Testplan,  string tablename,string QsNomi, string QdNomi)
        {
            string sSql;
            DataTable dt = new DataTable();
            switch (tablename)
            {
                case "cr3line1": sSql = string.Format(@"SELECT 
{3}.PartID as 'PartID',   {3}.sonplasfailure as 'F',{3}.Offload as 'Offload',
{3}.140430030 as 'Qs\r\nFM',  100*(({3}.140430030-{4})/{4}) as 'Qs\r\nFM\r\nShift%',
{3}.140430022 as 'Qd\r\nFM',  100*(({3}.140430022-{5})/{5}) as 'Qd\r\nFM\r\nShift%',
{3}.140430038 as 'Qs\r\nBal', 100*(({3}.140430038-{4})/{4}) as 'Qs\r\nBal\r\nShift%',
{3}.1404300210 as 'Qd\r\nBal', 100*(({3}.1404300210-{5})/{5}) as 'Qd\r\nBal\r\nShift%',
{3}.1404300111 as 'Qs\r\nFm\r\nMult',{3}.1404300110 as 'Qd\r\nFm\r\nMult',
{3}.1404300113 as 'Qs\r\nBal\r\nMult', {3}.1404300112 as 'Qd\r\nBal\r\nMult'
FROM {3} 
WHERE {3}.offload between {0}.5 and {1}
AND (type_id={2}) AND FamilyCode = '2' order by {3}.offload asc", starttime, endtime, Testplan, tablename, QsNomi, QdNomi);
                    break;
                default:
                    sSql = string.Format(@"SELECT 
{3}.partid as 'PartID',   {3}.sonplasfailure as 'F',{3}.offload as 'Offload',
{3}.obj36 as 'Qs\r\nFM',  100*(({3}.obj36-{4})/{4}) as 'Qs\r\nFM\r\nShift%',
{3}.obj31 as 'Qd\r\nFM',  100*(({3}.obj31-{5})/{5}) as 'Qd\r\nFM\r\nShift%',
{3}.obj50 as 'Qs\r\nBal', 100*(({3}.obj50-{4})/{4}) as 'Qs\r\nBal\r\nShift%',
{3}.obj45 as 'Qd\r\nBal', 100*(({3}.obj45-{5})/{5}) as 'Qd\r\nBal\r\nShift%',
{3}.obj72 as 'Qs\r\nFm\r\nMult',{3}.obj71 as 'Qd\r\nFm\r\nMult',   {3}.obj70 as 'Qs\r\nBal\r\nMult', {3}.obj69 as 'Qd\r\nBal\r\nMult'
FROM {3} 
WHERE {3}.offload between {0}.5 and {1}
AND (type_id={2}) AND FamilyCode = '2' order by {3}.offload asc", starttime, endtime, Testplan, tablename, QsNomi, QdNomi);
                    break;
            }
            dt = dbFunction.Query(ShopfloorDB.conn, sSql);
            return dt;
        }
        public static DataTable GetMasterTestResult_BAL(string starttime, string endtime, string Testplan, string tablename)
        {
            string sSql;
            DataTable dt = new DataTable();
            switch (tablename)
            {
                case "cr3line1": sSql = string.Format(@"SELECT 
{3}.PartID as 'PartID',   {3}.sonplasfailure as 'F',{3}.Offload as 'Offload',
{3}.140430030 as 'Qs\r\nFM',
{3}.140430022 as 'Qd\r\nFM',
{3}.140430038 as 'Qs\r\nBal',
{3}.1404300210 as 'Qd\r\nBal',
{3}.1404300111 as 'Qs\r\nFm\r\nMult',{3}.1404300110 as 'Qd\r\nFm\r\nMult',
{3}.1404300113 as 'Qs\r\nBal\r\nMult', {3}.1404300112 as 'Qd\r\nBal\r\nMult'
FROM {3} 
WHERE {3}.offload between {0}.5 and {1}
AND (type_id={2}) AND FamilyCode = '2' order by {3}.offload asc", starttime, endtime, Testplan, tablename);
                    break;
                default: sSql = string.Format(@"SELECT 
{3}.partid as 'PartID',   {3}.sonplasfailure as 'F',{3}.offload as 'Offload',
{3}.obj36 as '3_Qs\r\nFM',     {3}.obj31 as '4_Qd\r\nFM',        {3}.obj50 as '5_Qs\r\nBal',      {3}.obj45 as '6_Qd\r\nBal',
{3}.obj72 as '7_Qs_Fm\r\nMult',{3}.obj71 as '8_Qd_Fm\r\nMult',   {3}.obj70 as '9_Qs\r\nBal\r\nMult', {3}.obj69 as '10_Qd\r\nBal\r\nMult'
FROM {3} 
WHERE {3}.offload between {0}.5 and {1}
AND (type_id={2}) AND FamilyCode = '2' order by {3}.offload asc", starttime, endtime, Testplan, tablename);
                    break;
            }
            dt = dbFunction.Query(ShopfloorDB.conn, sSql);

            return dt;
        }

    }
}
